#include "supsql.h"
#include <QMessageBox>
#include <QSqlDatabase>
#include <QCoreApplication>
#include <QSqlQuery>
#include <QtDebug>
#include<QDebug>
#include<QSqlRecord>
#include<QVariant>
#include<QSqlError>
supsql * supsql::ptrSupSql = nullptr;
supsql::supsql(QObject *parent) : QObject(parent)
{

}

void supsql::init()
{
    if (QSqlDatabase::drivers().isEmpty())
        qDebug()<<"No database drivers found";

    m_db = QSqlDatabase::addDatabase("QSQLITE");
#if 1
    auto str=QCoreApplication::applicationDirPath()+"/data.db";
    qDebug()<<str;
#endif

    //m_db.setDatabaseName("D:\\git_project\\tgpss\\data.db");
    m_db.setDatabaseName(str);
//    m_db.setUserName("Sa");
//    m_db.setPassword("xsj200445");
    if (!m_db.open())
        qDebug()<<"db do not open";
}



int supsql::getStuCnt()
{
    QSqlQuery sql(m_db);
    sql.exec("select count(stu_id) from student;");
    int uicnt=0;
    while(sql.next())
    {
        uicnt=sql.value(0).toInt();
    }
    return uicnt;
}

int supsql::getTechCnt()
{
    QSqlQuery sql(m_db);
    sql.exec("select count(tech_id) from teacher;");
    int uicnt=0;
    while(sql.next())
    {
        uicnt=sql.value(0).toInt();
    }
    return uicnt;
}

int supsql::getCnt(QString s)
{
    QSqlQuery sql(m_db);
    sql.exec("select count(*) from "+s+";");
    sql.next();
    return sql.value(0).toInt();
}



QList<StuInfo> supsql::getPageStu(int page, int uicnt)
{
    QList<StuInfo> l;
    QSqlQuery sql(m_db);
    QString strsql = QString("select * from student order by stu_id limit %1 offset %2").
            arg(uicnt).arg(page*uicnt); //跳过page*uicnt条数据，选择uicnt条数据
    sql.exec(strsql);

    StuInfo info;
    while(sql.next())
    {
        info.stu_id= sql.value(0).toString();
        info.stu_name= sql.value(1).toString();
        info.stu_username = sql.value(2).toInt();
        info.stu_password = sql.value(3).toInt();
        l.push_back(info);
    }
    return l;
}

bool supsql::addStu(StuInfo info)
{
    QSqlQuery sql(m_db);
    QString sqlstr = QString("insert into student values(null,'%1','%2',%3,%4)").
            arg(info.stu_id).
            arg(info.stu_name).
            arg(info.stu_username).
            arg(info.stu_password);
    return sql.exec(sqlstr);
}

bool supsql::RemoveStu(QString id)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from student where stu_id = %1").arg(id));
}

InfoSet supsql::getInfo(Info *type, QString id, bool any)
{
    qDebug()<<"开始在数据库中查询信息";
    QString table=type->getTablename(), key=type->getcolname(type->get_ID_col());
    QString str=QString("select count(*) from %1").arg(table);
    if(!any)
            str+=QString(" where %2='%3'").arg(key,id);
    QSqlQuery sql0(m_db);
    sql0.exec(str);
    sql0.next();
    qDebug()<<str<<" := "<<sql0.value(0).toInt();
    InfoSet is(type,sql0.value(0).toInt());
    qDebug()<<"表格的列数为"<<is.colsize;
    str=QString("select * from %1 ").arg(table);
    if(!any)
            str+=QString(" where %2='%3'").arg(key,id);
    QSqlQuery sql(m_db);
    qDebug()<<str<<":="<<sql.exec(str);
    for(int i=0;i<is.size;i++)
    {
        sql.next();
        for(int j=0;j<is.colsize;j++)
        {
            is.item(i,j)=sql.value(j).toString();
        }
    }
    qDebug()<<"完成在数据库中的查询，现在返回信息";
    return is;
}







TechInfo supsql::GetTechInfo(QString id)
{
    QString st = QString("教师id为");
    qDebug()<<st<<id;
    QString str = QString("select * from teacher");
    QSqlQuery sql(m_db);
    sql.exec(str);
    TechInfo info;
    while (sql.next())
    {
        QString SqlId = sql.value(0).toString();
        if(SqlId == id)
        {

            info.tech_id = sql.value(0).toString();
            info.tech_school=sql.value(1).toString();
            info.tech_name = sql.value(2).toString();
            info.tech_address=sql.value(3).toString();
            info.tech_phone=sql.value(4).toString();
            info.tech_username = sql.value(5).toString();
            info.tech_password = sql.value(6).toString();
            return info;
        }
    }
    return info;
    qDebug()<<"Not Find";
}

StuExp supsql::GetStuExpInfo(QString id)
{
    QString st = QString("学生id为");
    qDebug()<<st<<id;
    QString str = QString("select * from stu_exp");
    QSqlQuery sql(m_db);
    sql.exec(str);
    StuExp info;
    while (sql.next())
    {
        QString SqlId = sql.value(0).toString();
        if(SqlId == id)
        {

            info.stu_id = sql.value(0).toString();
            info.primary_school = sql.value(1).toString();
            info.middle_school = sql.value(2).toString();
            info.high_school = sql.value(3).toString();
            info.university = sql.value(4).toString();
            return info;
        }
    }
    return info;
    qDebug()<<"Not Find";
}



QList<Sci_Result> supsql::getSciSet(QString tch_id)
{
    QSqlQuery sql(m_db);
    QString check="select * from sci_result where tech_id="+tch_id;
    sql.exec(check);
    QList<Sci_Result> list;
    while(sql.next())
    {
        Sci_Result sc;
        sc.tch_id = sql.value(0).toString();
        sc.sci_result_id = sql.value(1).toString();
        sc.sci_result_name = sql.value(2).toString();
        list.push_back(sc);
    }

    return list;
}

QList<Work_exp> supsql::getWorkExp(QString tch_id)
{
    QSqlQuery sql(m_db);
    QString check="select * from work_exp where tech_id="+tch_id;
    qDebug()<<tch_id;
    sql.exec(check);
    QList<Work_exp> list;
    while(sql.next())
    {
        Work_exp we;
        we.tch_id=sql.value(0).toString();
        we.tch_work_id=sql.value(1).toString();
        we.start_time=sql.value(2).toString();
        we.end_time=sql.value(3).toString();
        we.work_place=sql.value(4).toString();
        list.push_back(we);
    }
    qDebug()<<"Worklist.size="<<list.size();
    return list;
}

QList<Develop> supsql::getDeleveop(QString tch_id)
{
    QSqlQuery sql(m_db);
    QString check="select * from dev_ana where tech_id="+tch_id;
    sql.exec(check);
    QList<Develop> list;
    while(sql.next())
    {
        Develop temp;
        temp.now_condition=sql.value(1).toString();
        temp.tch_id=sql.value(0).toString();
        temp.grow_up=sql.value(2).toString();
        temp.dev_method=sql.value(3).toString();
        list.push_back(temp);
    }
    return list;
}

QList<Recruit> supsql::getRecruit()
{
    QSqlQuery sql(m_db);
    QString check="select * from recruit";
    sql.exec(check);
    QList<Recruit> list;
    while(sql.next())
    {
        Recruit temp;
        temp.recruit_id=sql.value(0).toString();
        temp.pos=sql.value(1).toString();
        temp.salary=sql.value(2).toString();
        list.push_back(temp);
    }
    return list;
}

QList<Appc> supsql::getAppc()
{
    QSqlQuery sql(m_db);
    QString check="select * from application";
    sql.exec(check);
    QList<Appc> list;
    while(sql.next())
    {
        Appc temp;
        temp.app_id=sql.value(0).toString();
        temp.tech_id=sql.value(1).toString();
        temp.pos=sql.value(2).toString();
        temp.salary=sql.value(3).toString();
        list.push_back(temp);
    }
    return list;
}

QList<Intern> supsql::getIntern(QString tch_id)
{
    QSqlQuery sql(m_db);
    QString check="select * from intern where tech_id="+tch_id;
    sql.exec(check);
    QList<Intern> list;
    while(sql.next())
    {
        Intern temp;
        temp.tech_id=sql.value(0).toString();
        temp.intern_id=sql.value(1).toString();
        temp.intern_name=sql.value(2).toString();
        list.push_back(temp);
    }
    return list;
}

bool supsql::ClearStuTable()
{
    QSqlQuery sql(m_db);
    return sql.exec("delete from student");
}

bool supsql::UpdateStuInfo(StuInfo info)
{
    QSqlQuery sql(m_db);
    QString sqlstr = QString("update student set stu_name = '%1', stu_address = '%2',  stu_phonenum= '%3' where stu_id =%4").
            arg(info.stu_name).
            arg(info.stu_address).
            arg(info.stu_phonenum).
            arg(info.stu_id);
    qDebug()<<sqlstr;
    return sql.exec(sqlstr);
}


QList<TechInfo> supsql::getPageTech(int page,int uicnt)
{
    QList<TechInfo> l;
    QSqlQuery sql(m_db);
    QString strsql = QString("select * from teacher order by tech_id limit %1 offset %2").
            arg(uicnt).arg(page*uicnt); //跳过page*uicnt条数据，选择uicnt条数据
    sql.exec(strsql);

    TechInfo info;
    while(sql.next())
    {
        info.tech_id= sql.value(0).toString();
        info.tech_name= sql.value(2).toString();
        info.tech_school= sql.value(1).toString();
        info.tech_address= sql.value(3).toString();
        info.tech_phone= sql.value(4).toString();
        info.tech_username= sql.value(5).toInt();
        info.tech_password= sql.value(6).toInt();
        l.push_back(info);
    }
    return l;
}

bool supsql::IsExistTech(TechInfo info)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("select *from teacher where tech_name='%1' and tech_username =%2 ").arg(info.tech_name).arg(info.tech_username));

}

bool supsql::IsExistStu(StuInfo info)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("select *from student where stu_name='%1' and stu_username =%2 ").arg(info.stu_name).arg(info.stu_username));
}

bool supsql::IsExistEval(StuEvalTech info)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("select *from stu_eval_tech where stu_id='%1' and tech_id =%2 ").arg(info.stu_id).arg(info.tech_id));
}

bool supsql::changeTechInfo(TechInfo info)
{
    QSqlQuery sql(m_db);
    QString sqlstr = QString("update teacher set tech_name = '%1', tech_address = '%2',  tech_phonenum= '%3', tech_school= '%4' where tech_id ='%5'").
            arg(info.tech_name).
            arg(info.tech_address).
            arg(info.tech_phone).
            arg(info.tech_school).
            arg(info.tech_id);
    qDebug()<<sqlstr;
    return sql.exec(sqlstr);
}

bool supsql::AddTech(TechInfo info)
{
    info.tech_password = 666666;
    QSqlQuery sql(m_db);
    QString sqlstr = QString("insert into teacher values(null,'%1',%2,%3,'%4','%5','%6')").
            arg(info.tech_id).
            arg(info.tech_school).
            arg(info.tech_name).
            arg(info.tech_address).
            arg(info.tech_phone).
            arg(info.tech_password).arg(info.tech_username);
       // qDebug()<<info.tech_name<<info.theis_info<<info.achieve_info<<info.tech_exp<<info.tech_username;
    return sql.exec(sqlstr);

}

bool supsql::AddEval(StuEvalTech info)
{
    QSqlQuery sql(m_db);
    qDebug()<<"test"<<info.stu_id;
    QString sqlstr = QString("insert into stu_eval_tech values('%1','%2','%3')").
            arg(info.stu_id).
            arg(info.tech_id).arg(info.stu_eval);
    return sql.exec(sqlstr);
}

bool supsql::RemoveTech(int username)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from teacher where tech_username=%1 ").arg(username));
}

bool supsql::UpdateTechPwd(TechInfo info)
{
    QSqlQuery sql(m_db);
    //qDebug()<<info.name<<info.username<<info.address<<info.phone<<info.Aur<<info.password;
    QString sqlstr = QString("update teacher set tech_password =%1  where tech_username =%2 and tech_name='%3'").
            arg(info.tech_password).
            arg(info.tech_username).
            arg(info.tech_name);
    return sql.exec(sqlstr);
}

bool supsql::UpdateStuPwd(StuInfo info)
{
    QSqlQuery sql(m_db);
    //qDebug()<<info.name<<info.username<<info.address<<info.phone<<info.Aur<<info.password;
    QString sqlstr = QString("update student set stu_password =%1  where stu_username =%2 and stu_name='%3'").
            arg(info.stu_password).
            arg(info.stu_username).
            arg(info.stu_name);
    return sql.exec(sqlstr);
}
//陈嘉俊 修改学生学习经历
bool supsql::UpdateStuExp(StuExp info)
{
    QSqlQuery sql(m_db);
    //qDebug()<<info.name<<info.username<<info.address<<info.phone<<info.Aur<<info.password;
    QString sqlstr = QString("update stu_exp set primary_school='%1', middle_school='%2' , high_school='%3', university='%4' where stu_id='%5'").
            arg(info.primary_school).
            arg(info.middle_school).
            arg(info.high_school).
            arg(info.university).
            arg(info.stu_id);
    qDebug()<<sqlstr;
    return sql.exec(sqlstr);
}

bool supsql::UpdateInfo(QString table, QString colname, QString content, QString keyname, QString id, bool delay)//delay默认值为0
{
    QString sqlstr = QString("update %1 set %2='%3' where %4='%5'")
            .arg(table)
            .arg(colname)
            .arg(content)
            .arg(keyname)
            .arg(id);
    qDebug()<<sqlstr;
    if(delay)
    {
        sqlList<<sqlstr;
        return true;
    }
    qDebug()<<sqlstr;
    QSqlQuery sql(m_db);
    return sql.exec(sqlstr);

}

bool supsql::executeDelayedCommand()
{
    bool flag=true;
    QSqlQuery sql(m_db);
    for(QString str:sqlList)
    {
        flag&= sql.exec(str);
        qDebug()<<str;
    }
    clearCommand();
    return flag;
}








